﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using InstrumentCheck.Module;
using InstrumentCheck.DAL;
using InstrumentCheck.SQLHelper;

namespace InstrumentCheck.DAL
{
    public class Maintain_dal
    {
        public Maintain_dal()
        { }

        public List<Item> GetItemList_dal(string strFasNO)
        {
            string sql = "SELECT *,";
            sql += " dbo.f_GetKeepDeptNO(Fas_NO,Fas_Type) as KeepDeptNO,";
            sql += " dbo.f_GetKeepEmpNO(Fas_NO,Fas_Type) as KeeperNO,";
            sql += " dbo.f_GetKeepEmpName(Fas_NO,Fas_Type) as KeeperName,";
            sql += " dbo.f_GetItemName(Fas_NO,Fas_Type) as ItemName,";
            sql += " dbo.f_GetItemBrand(Fas_NO,Fas_Type) as ItemBrand,";
            sql += " dbo.f_GetItemSpec(Fas_NO,Fas_Type) as ItemSpec,";
            sql += " dbo.f_GetItemModelNO(Fas_NO,Fas_Type) as ItemModelNO,";
            sql += " dbo.f_IsProcess(Fas_NO) as IsProcess";
            sql += " FROM Correct_Item";
            if (strFasNO != string.Empty)
            {
                sql = " WHERE Fas_NO='" + strFasNO + "'";
            }
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, null);
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            else
                return GenerateItemList(ds.Tables[0]);
        }

        public List<Item> GetItemListByCondition_dal(string strFasNO, string strItemName, string strDeptNO)
        {
            string sql = "SELECT *,";
            sql += " dbo.f_GetKeepDeptNO(Fas_NO,Fas_Type) as KeepDeptNO,";
            sql += " dbo.f_GetKeepEmpNO(Fas_NO,Fas_Type) as KeeperNO,";
            sql += " dbo.f_GetKeepEmpName(Fas_NO,Fas_Type) as KeeperName,";
            sql += " dbo.f_GetItemName(Fas_NO,Fas_Type) as ItemName,";
            sql += " dbo.f_GetItemBrand(Fas_NO,Fas_Type) as ItemBrand,";
            sql += " dbo.f_GetItemSpec(Fas_NO,Fas_Type) as ItemSpec,";
            sql += " dbo.f_GetItemModelNO(Fas_NO,Fas_Type) as ItemModelNO,";
            sql += " dbo.f_IsProcess(Fas_NO) as IsProcess";
            sql += " FROM Correct_Item";
            sql += "  WHERE Fas_NO like '%" + strFasNO + "%' and dbo.f_GetItemName(Fas_NO,Fas_Type) like N'%" + strItemName + "%' and dbo.f_GetKeepDeptNO(Fas_NO,Fas_Type) like '%" + strDeptNO + "%'";
            
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            //SqlParameter[] para = new SqlParameter[3];
            //para[0] = SQLHelper.SQLHelper.CreateParameter("FasNO", SqlDbType.VarChar, strFasNO, ParameterDirection.Input);
            //para[1] = SQLHelper.SQLHelper.CreateParameter("ItemName", SqlDbType.NVarChar, strItemName, ParameterDirection.Input);
            //para[2] = SQLHelper.SQLHelper.CreateParameter("DeptNO", SqlDbType.VarChar, strDeptNO, ParameterDirection.Input);

            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, null);
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            else
                return GenerateItemList(ds.Tables[0]);
        }

        private List<Item> GenerateItemList(DataTable dt)
        {
            List<Item> list =null;
            if (dt.Rows.Count > 0)
                list = new List<Item>();
            foreach (DataRow row in dt.Rows)
            {
                Item item = new Item();
                item.Creater = new User();
                item.Creater.EmpNO = row["Creater"].ToString();
                item.CreateDate = Convert.ToDateTime(row["Create_Date"]);
                item.AssetNO = row["Fas_NO"].ToString();
                item.AssetType = (AssetType)row["Fas_Type"];
                item.Brand = row["ItemBrand"].ToString();
                if (row["Buy_AMT"] != DBNull.Value && row["Buy_AMT"].ToString() != string.Empty)
                    item.BuyAmount = Convert.ToDouble(row["Buy_AMT"]);
                item.BuyDate = Convert.ToDateTime(row["Buy_date"]);
                item.ChiName = row["ItemName"].ToString();
                if (row["Correct_AMT"] != DBNull.Value && row["Correct_AMT"].ToString() != string.Empty)
                    item.CorrectAmount = Convert.ToDouble(row["Correct_AMT"]);
                if (row["Exp_currency"] != DBNull.Value && row["Exp_currency"].ToString() != string.Empty)
                    item.CorrectCurrencyType = (CurrencyType)Enum.Parse(typeof(CurrencyType), row["Exp_currency"].ToString());// (CurrencyType)row["Exp_currency"];
                item.CorrectCycle = (CorrectCycle)row["Correct_period"];
                if (row["Correct_result"] != DBNull.Value && row["Correct_result"].ToString() != string.Empty)
                    item.CorrectResult = (CorrectResult)row["Correct_result"];
                item.CorrectType = (CorrectType)row["Correct_type"];
                if (row["Buy_currency"] != DBNull.Value && row["Buy_currency"].ToString() != string.Empty)
                    item.CurrencyType = (CurrencyType)Enum.Parse(typeof(CurrencyType), row["Buy_currency"].ToString());// (CurrencyType)row["Buy_currency"];
                item.CurrentStatus = (AssetStatus)row["Status"];
                item.FirstCorrectTime = Convert.ToDateTime(row["First_correct"]);
                
                item.ModelNO = row["ItemModelNO"].ToString();
                item.NeedCorrectDate = Convert.ToDateTime(row["Correct_date"]);
                item.PRNO = row["PR_NO"].ToString();
                item.Remark = row["remark"].ToString();
                item.Spec = row["ItemSpec"].ToString();
                item.Standard = row["standard"].ToString();
                item.ProcessStatus = Convert.ToInt16(row["IsProcess"]);

                item.ModifyBy = new User();
                item.ModifyBy.EmpNO = row["modify_by"].ToString();

                item.Keeper = new User();
                item.Keeper.DeptNO = row["KeepDeptNO"].ToString();
                item.Keeper.EmpNO = row["KeeperNO"].ToString();
                item.Keeper.EmpChineseName = row["KeeperName"].ToString();

                item.ItemAttachment = GetActiveAttachListbyFasNO(row["Fas_NO"].ToString());
               
                list.Add(item);
            }
            return list;
        }

        public List<Attach> GetActiveAttachListbyFasNO(string strFasNO)
        {
            string sql = "SELECT * FROM Tbl_Attachment_Active WHERE Fas_NO='" + strFasNO + "'";
            
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, null);
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            else
                return GenerateActiveAttachList(ds.Tables[0]);
        }

        private List<Attach> GenerateActiveAttachList(DataTable dt)
        {
            List<Attach> list = null;
            if (dt.Rows.Count > 0)
                list = new List<Attach>();
            foreach (DataRow row in dt.Rows)
            {
                Attach attach = new Attach();
                attach.ClientName = row["ClientName"].ToString();
                attach.FAS_NO = row["FAS_NO"].ToString();
                //attach.FormNO= row["Fas_NO"].ToString();
                attach.SystemName = row["SystemName"].ToString();
                attach.Type = (AttachmentType)row["Type"];
                list.Add(attach);
            }
            return list;
        }

        public List<ApplyForm> GeAllFormListbyFasNO_dal(string strFasNO)
        {
            string sql = "SELECT * FROM Correct WHERE Fas_NO='" + strFasNO + "' AND FormStatus in (1,2) Order by Number";

            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, null);
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            else
                return GenerateFormList(ds.Tables[0]);
        }

        private List<ApplyForm> GenerateFormList(DataTable dt)
        {
            List<ApplyForm> list =null;
            if (dt.Rows.Count > 0)
                list = new List<ApplyForm>();
            foreach (DataRow row in dt.Rows)
            {
                ApplyForm form = new ApplyForm();
                //form.Applicant = null;
                //form.ApplyDate = System.DateTime.MinValue;
                form.ApplyReason = row["Reason"].ToString();
                //form.CloseTime = System.DateTime.MaxValue;
                //form.Creater = null;
                form.FormID = Convert.ToInt32(row["CorrectID"]);
                form.FormNO = Convert.ToInt32(row["Number"]);
                //form.FormRoutingHistory = null;
                form.FormStatus = (FormStatus)row["FormStatus"];
                //form.FormType = (FormType)row["Type"];
                //form.instanceID = new Guid(row["CorrectInstanceID"].ToString());
                form.Item = new Item();
                form.Item.NeedCorrectDate = Convert.ToDateTime(row["Correct_date"]);
                form.Item.CorrectCycle = (CorrectCycle)row["Correct_period"];

                list.Add(form);
            }
            return list;
        }

        public void SaveItem_dal(Item item)
        {
            SqlConnection cn = Helper.GetConnection();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = null;

            try
            {
                Helper.OpenConnection(cn);
                trans = cn.BeginTransaction();
                cmd.Connection = cn;
                cmd.Transaction = trans;

                SaveCorrectItem(item, cmd);
                SaveActiveAttachment(item, cmd);
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }

                if (cn != null)
                    Helper.CloseConnection(cn);
            }
        }

        public void UpdateItem_dal(Item item,string OldFasNO)
        {
            SqlConnection cn = Helper.GetConnection();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = null;

            try
            {
                Helper.OpenConnection(cn);
                trans = cn.BeginTransaction();
                cmd.Connection = cn;
                cmd.Transaction = trans;

                string deleteSql = "DELETE FROM Correct_Item WHERE Fas_NO=@FAS_NO";
                cmd.Parameters.Clear();
                cmd.Parameters.Add(new SqlParameter("FAS_NO", SqlDbType.VarChar));
                cmd.Parameters["FAS_NO"].Value = OldFasNO;
                cmd.CommandText = deleteSql;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                SaveCorrectItem(item, cmd);

                deleteSql = "DELETE FROM Tbl_Attachment_Active WHERE Fas_NO=@FAS_NO";
                cmd.Parameters.Clear();
                cmd.Parameters.Add(new SqlParameter("FAS_NO", SqlDbType.VarChar));
                cmd.Parameters["FAS_NO"].Value = OldFasNO;
                cmd.CommandText = deleteSql;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                SaveActiveAttachment(item, cmd);

                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }

                if (cn != null)
                    Helper.CloseConnection(cn);
            }
        }

        private void SaveCorrectItem(Item item, SqlCommand cmd)
        {
            cmd.Parameters.Clear();
            
            cmd.Parameters.Add(new SqlParameter("Creater", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("Create_date", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("Fas_Type", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("Fas_NO", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("PR_NO", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("Buy_Date", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("Buy_Currency", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("Buy_AMT", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("First_Correct", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("Correct_Date", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("Exp_Currency", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("Correct_AMT", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("Correct_Period", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("Status", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("Correct_result", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("Correct_Type", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("Remark", SqlDbType.NVarChar));
            cmd.Parameters.Add(new SqlParameter("Standard", SqlDbType.NVarChar));
            cmd.Parameters.Add(new SqlParameter("modify_by", SqlDbType.VarChar));
            
            cmd.Parameters["Creater"].Value = item.Creater.EmpNO;
            cmd.Parameters["Create_date"].Value = item.CreateDate;
            cmd.Parameters["Fas_Type"].Value = (int)item.AssetType;
            cmd.Parameters["Fas_NO"].Value = item.AssetNO;
            cmd.Parameters["PR_NO"].Value = item.PRNO;
            cmd.Parameters["Buy_Date"].Value = item.BuyDate;
            cmd.Parameters["Buy_Currency"].Value = item.CurrencyType;
            cmd.Parameters["Buy_AMT"].Value = item.BuyAmount;
            cmd.Parameters["First_Correct"].Value = item.FirstCorrectTime;
            cmd.Parameters["Correct_Date"].Value = item.NeedCorrectDate;
            cmd.Parameters["Exp_Currency"].Value = item.CorrectCurrencyType;
            cmd.Parameters["Correct_AMT"].Value = item.CorrectAmount;
            cmd.Parameters["Correct_Period"].Value = (int)item.CorrectCycle;
            cmd.Parameters["Status"].Value = (int)item.CurrentStatus;
            cmd.Parameters["Correct_result"].Value = (int)item.CorrectResult;
            cmd.Parameters["Correct_Type"].Value = (int)item.CorrectType;
            cmd.Parameters["Remark"].Value = item.Remark;
            cmd.Parameters["Standard"].Value = item.Standard;
            cmd.Parameters["modify_by"].Value = item.ModifyBy.EmpNO;

            cmd.CommandText = "sp_SaveCorrectItem";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();

        }
        private void SaveActiveAttachment(Item item, SqlCommand cmd)
        {
            if (item.ItemAttachment == null)
                return;

            List<Attach> attachList = item.ItemAttachment;

            if (attachList.Count == 0)
                return;

            foreach (Attach attach in attachList)
            {
                cmd.Parameters.Clear();

                cmd.Parameters.Add(new SqlParameter("FAS_NO", SqlDbType.VarChar));
                cmd.Parameters.Add(new SqlParameter("SystemName", SqlDbType.VarChar));
                cmd.Parameters.Add(new SqlParameter("ClientName", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("Type", SqlDbType.Int));

                cmd.Parameters["FAS_NO"].Value = item.AssetNO;
                cmd.Parameters["SystemName"].Value = attach.SystemName;
                cmd.Parameters["ClientName"].Value = attach.ClientName;
                cmd.Parameters["Type"].Value = (int)attach.Type;

                cmd.CommandText = "sp_SaveActiveAttachment";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }

        }
    }
}
